﻿namespace EasyWeb.Test
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Text;
    using System.Threading.Tasks;
    using System.Transactions;
    using EasyWeb.Extensions;
    using Microsoft.VisualStudio.TestTools.UnitTesting;

    public abstract class BaseTest
    {
        protected const string DatabaseTableConstraintTypeName_PrimaryKey = "PRIMARY KEY";

        public TestContext TestContext { get; set; }

        protected TestDbContext TestDbContext { get; private set; }

        [TestInitialize]
        public virtual void TestInitialize()
        {
            EWExtensionManager.Initialize(null);

            EWHelpers.Async.RunSync(async () => await CleanupDatabaseAsync());

            this.TestDbContext = new TestDbContext();
        }

        [TestCleanup]
        public virtual void TestCleanup()
        {
            if (this.TestDbContext != null)
            {
                this.TestDbContext.Dispose();
            }

            EWHelpers.Async.RunSync(async () => await CleanupDatabaseAsync());
        }

        protected virtual async Task CleanupDatabaseAsync()
        {
            // use new context due to avoid influence of test cases
            var context = default(TestDbContext);
            var transaction = default(TransactionScope);

            try
            {
                transaction = EWHelpers.Data.BeginTransaction();
                context = new TestDbContext();

                await CleanupDatabaseFieldsAsync(context);
                await CleanupDatabaseEntitiesAsync(context);
                await CleanupDatabaseSelectionItemsAsync(context);
                await CleanupDatabaseSelectionsAsync(context);
                await CleanupDatabaseConfigurationsAsync(context);
                await CleanupDatabaseCulturesAsync(context);
                await CleanupDatabaseUserTablesAsync(context);

                transaction.Complete();
            }
            finally
            {
                if (transaction != null) transaction.Dispose();
                if (context != null) context.Dispose();
            }
        }

        protected virtual async Task CleanupDatabaseFieldsAsync(TestDbContext context)
        {
            await context.Database.ExecuteSqlCommandAsync("DELETE ew_tbl_fields");
        }

        protected virtual async Task CleanupDatabaseEntitiesAsync(TestDbContext context)
        {
            await context.Database.ExecuteSqlCommandAsync("DELETE ew_tbl_entities");
        }

        protected virtual async Task CleanupDatabaseSelectionItemsAsync(TestDbContext context)
        {
            await context.Database.ExecuteSqlCommandAsync("DELETE ew_tbl_selection_items");
        }

        protected virtual async Task CleanupDatabaseSelectionsAsync(TestDbContext context)
        {
            await context.Database.ExecuteSqlCommandAsync("DELETE ew_tbl_selections");
        }

        protected virtual async Task CleanupDatabaseConfigurationsAsync(TestDbContext context)
        {
            // HACK: update the following code if the XML schema has change

            const string Update_Configurations_Sql_Format = "UPDATE ew_tbl_configurations SET ew_xml_value_1 = '{0}' WHERE ew_uid_configuration_id = CONVERT(UNIQUEIDENTIFIER, '{1}')";
            await context.Database.ExecuteSqlCommandAsync(string.Format(Update_Configurations_Sql_Format, "<value><value>en</value><client>False</client><default>True</default></value>", EWCommon.ConfigurationIdDefaultLanguage));
            await context.Database.ExecuteSqlCommandAsync(string.Format(Update_Configurations_Sql_Format, "<value></value>", EWCommon.ConfigurationIdSystemPrefix));

            var deleteSql = new StringBuilder("DELETE ew_tbl_configurations WHERE ew_uid_configuration_id NOT IN (");
            deleteSql.AppendFormat("CONVERT(UNIQUEIDENTIFIER, '{0}'),", EWCommon.ConfigurationIdDefaultLanguage);
            deleteSql.AppendFormat("CONVERT(UNIQUEIDENTIFIER, '{0}'),", EWCommon.ConfigurationIdSystemPrefix);
            deleteSql.Replace(',', ')', deleteSql.Length - 1, 1);
            await context.Database.ExecuteSqlCommandAsync(deleteSql.ToString());

            await context.Database.ExecuteSqlCommandAsync("UPDATE ew_tbl_configurations SET ew_nvc_modified_by = 'System'");
        }

        protected virtual async Task CleanupDatabaseCulturesAsync(TestDbContext context)
        {
            await context.Database.ExecuteSqlCommandAsync("UPDATE ew_tbl_cultures SET ew_b_enabled = 0");

            var deleteSql = new StringBuilder("DELETE ew_tbl_cultures WHERE ew_uid_culture_id NOT IN (");
            deleteSql.AppendFormat("CONVERT(UNIQUEIDENTIFIER, '{0}'),", EWCommon.CultureIdEnglish);
            deleteSql.AppendFormat("CONVERT(UNIQUEIDENTIFIER, '{0}'),", EWCommon.CultureIdChinese);
            deleteSql.Replace(',', ')', deleteSql.Length - 1, 1);
            await context.Database.ExecuteSqlCommandAsync(deleteSql.ToString());

            await context.Database.ExecuteSqlCommandAsync("UPDATE ew_tbl_cultures SET ew_nvc_modified_by = 'System'");
        }

        protected virtual async Task CleanupDatabaseUserTablesAsync(TestDbContext context)
        {
            var tables = await GetDatabaseUserTablesAsync(context.Database.Connection);
            foreach (var table in tables)
            {
                await context.Database.ExecuteSqlCommandAsync(string.Format("DROP TABLE {0}", table.Key));
            }
        }

        protected virtual async Task<IDictionary<string, IList<Tuple<string, string, bool>>>> GetDatabaseUserTablesAsync(DbConnection connection)
        {
            var result = new Dictionary<string, IList<Tuple<string, string, bool>>>();

            const string CommandText = "SELECT [TABLE_NAME], [COLUMN_NAME], [DATA_TYPE], [IS_NULLABLE] from INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_SCHEMA] = N'dbo'";
            await ExecuteReaderAsync(connection, CommandText, reader =>
            {
                var tableName = reader.GetString(0);
                if (!IsSystemDatabaseTable(tableName))
                {
                    var columnName = reader.GetString(1);
                    var dataType = reader.GetString(2);
                    var isNullable = reader.GetString(3).Equals("YES", StringComparison.OrdinalIgnoreCase);
                    var collection = default(IList<Tuple<string, string, bool>>);
                    if (result.ContainsKey(tableName))
                    {
                        collection = result[tableName];
                    }
                    else
                    {
                        collection = new List<Tuple<string, string, bool>>();
                        result.Add(tableName, collection);
                    }
                    collection.Add(new Tuple<string, string, bool>(columnName, dataType, isNullable));
                }
            });

            return result;
        }

        protected virtual async Task<IDictionary<string, IList<Tuple<string, string>>>> GetDatabaseUserTableConstraintsAsync(DbConnection connection)
        {
            var result = new Dictionary<string, IList<Tuple<string, string>>>();

            const string CommandText = "SELECT [TABLE_NAME], [CONSTRAINT_NAME], [CONSTRAINT_TYPE] from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE [TABLE_SCHEMA] = N'dbo' AND [CONSTRAINT_SCHEMA] = N'dbo'";
            await ExecuteReaderAsync(connection, CommandText, reader =>
            {
                var tableName = reader.GetString(0);
                if (!IsSystemDatabaseTable(tableName))
                {
                    var constraintName = reader.GetString(1);
                    var constraintType = reader.GetString(2);
                    var collection = default(IList<Tuple<string, string>>);
                    if (result.ContainsKey(tableName))
                    {
                        collection = result[tableName];
                    }
                    else
                    {
                        collection = new List<Tuple<string, string>>();
                        result.Add(tableName, collection);
                    }
                    collection.Add(new Tuple<string, string>(constraintName, constraintType));
                }
            });

            return result;
        }

        protected virtual async Task ExecuteReaderAsync(DbConnection connection, string commandText, Action<DbDataReader> executor)
        {
            var factory = DbProviderFactories.GetFactory(connection);
            DbCommand command = null;
            DbDataReader reader = null;
            try
            {
                command = factory.CreateCommand();
                command.Connection = connection;
                command.CommandText = commandText;
                command.CommandType = CommandType.Text;

                if (connection.State == ConnectionState.Closed)
                {
                    await connection.OpenAsync();
                }

                reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
                while (await reader.ReadAsync())
                {
                    executor(reader);
                }
            }
            finally
            {
                if (reader != null) reader.Dispose();
                if (command != null) command.Dispose();
            }
        }

        private static bool IsSystemDatabaseTable(string tableName)
        {
            return tableName.StartsWith("ew_tbl_", StringComparison.OrdinalIgnoreCase);
        }
    }
}
